--Crash
INSERT INTO Crash (StateCaseNumber, CrashDate, OfficerName, Parish, City, FirstHarmfulEvent, CrashManner,
                   RoadwaySurfaceCondition, ModifiedDate)

SELECT JSON_VALUE(DataFile, '$.StateCaseNumber') AS StateCaseNumber
      ,JSON_VALUE(Datafile, '$.CrashDate') AS CrashDate
	  ,JSON_VALUE(Datafile, '$.InvestigatingOfficerFirstName') + ' ' +
	        JSON_VALUE(Datafile, '$.InvestigatingOfficerLastName') AS OfficerName
      ,JSON_VALUE(Datafile, '$.Parish.Name') AS Parish
	  ,JSON_VALUE(Datafile, '$.City.Name') AS City
	  ,JSON_VALUE(Datafile, '$.FirstHarmfulEvent.Name') AS FirstHarmfulEvent
	  ,JSON_VALUE(Datafile, '$.CrashManner.Name') AS CrashManner
	  ,JSON_VALUE(Datafile, '$.RoadwaySurfaceCondition.Name') AS RoadwaySurfaceCondition
	  ,GetDate() AS ModifiedDate
FROM JSONData;

--Driver
INSERT INTO Driver (StateCaseNumber, VehicleNumber, DriverNumber, VIN, LicenseStatus, LicenseState,
                     AlcoholInterlockPresence, DrugTestStatus, ModifiedDate)

SELECT JSON_VALUE(DataFile, '$.StateCaseNumber') AS StateCaseNumber,
        Vehicles.VehicleNumber,
        Driver.DriverNumber,
		Vehicles.VIN,
        Driver.LicenseStatus,
        Driver.LicenseState,
        Driver.AlcoholInterlockPresence,
        Driver.DrugTestStatus,
      GetDate() AS ModifiedDate
FROM JSONData
cross apply (
    select 
    VehicleNumber=ROW_NUMBER() over (order by (SELECT NULL)), 
    * 
    from OpenJson(datafile, '$.Vehicles') 
    with 
    (
        Vin nvarchar(50),
        Driver nvarchar(max) AS JSON
    )
) as Vehicles
cross apply (
select DriverNumber=ROW_NUMBER() over (order by (SELECT NULL)),*
FROM
OpenJson(Vehicles.Driver, '$')

with (
    LicenseStatus nvarchar(50) '$.DriverLicenseStatus.Name',
    LicenseState nvarchar(50) '$.DriverLicenseState.Name',
    AlcoholInterlockPresence nvarchar(50) '$.AlcoholInterlockPresence.Name',
    DrugTestStatus nvarchar(50) '$.ConditionInfo.DrugTestStatus.Name'
)
 ) as Driver

--Vehicle
INSERT INTO Vehicle (StateCaseNumber, VehicleNumber,VIN, LicensePlate, LicensePlateYear,
                     Make, BodyType, ModifiedDate)

SELECT JSON_VALUE(DataFile, '$.StateCaseNumber') AS StateCaseNumber,
        Vehicles.VehicleNumber,
		Vehicles.VIN,
		Vehicles.TagNumber AS LicensePlate,
        Vehicles.TagYear AS LicensePlateYear,
        Vehicles.Make AS Make,
        Vehicles.BodyType AS BodyType,
      GetDate() AS ModifiedDate
FROM JSONData
cross apply (
    select 
    VehicleNumber=ROW_NUMBER() over (order by (SELECT NULL)), 
    * 
    from OpenJson(datafile, '$.Vehicles') 
    with 
    (
        Vin nvarchar(50),
		TagNumber nvarchar(20),
		TagYear nvarchar(4),
		Make nvarchar(100) '$.Make.Name',
		BodyType nvarchar(100) '$.BodyType.Name'
    )
) as Vehicles;